Databases: PyMongo

Working with PyMongo¶

For all of these examples, we're going to be working with the "lagos" collection in the "air-quality" database. Before we can do anything else, we need to bring in pandas (which we won't use until the very end), pprint (a module that lets us see the data in an understandable way), and PyMongo (a library for working with MongoDB databases).

Databases¶

Data comes to us in lots of different ways, and one of those ways is in a database. A database is a collection of data.

Servers and Clients¶

Next, we need to connect to a server. A database server is where the database resides; it can be accessed using a client. Without a client, a database is just a collection of information that we can't work with, because we have no way in. We're going to be learning more about a database called MongoDB, and we'll use PrettyPrinter to make the information it generates easier to understand. Here's how the connection works:

Semi-structured Data¶

Databases are designed to work with either structured data or semi-structured data. In this part of the course, we're going to be working with databases that contain semi-structured data. Data is semi-structured when it has some kind of organizing logic, but that logic can't be displayed using rows and columns. Your email account contains semi-structured data if it’s divided into sections like Inbox, Sent, and Trash. If you’ve ever seen tweets from Twitter grouped by hashtag, that’s semi-structured data too. Semi-structured data is also used in sensor readings, which is what we'll be working with here.

Exploring a Database¶

So, now that we're connected to a server, let's take a look at what's there. Working our way down the specificity scale, the first thing we need to do is figure out which databases are on this server. To see which databases on the server, we'll use the list_databases method, like this:

It looks like this server contains four databases: "admin", "air-quality", "config", and "local". We're only interested in "air-quality", so let's connect to that one:

In MongoDB, a database is a container for collections. Each database gets its own set of files, and a single MongoDB server typically has multiple databases.

Collections¶

Let's use a for loop to take a look at the collections in the "air-quality" database:

As you can see, there are three actual collections here: "nairobi", "lagos", and "dar-es-salaam". Since we're only interested in the "lagos" collection, let's get it on its own like this:

Documents¶

A MongoDB document is an individual record of data in a collection, and is the basic unit of analysis in MongoDB. Documents come with metadata that helps us understand what the document is; we'll get back to that in a minute. In the meantime, let's use the count_documents method to see how many documents the "lagos" collection contains:

Practice

Try it yourself! Bring in all the necessary libraries and modules, then connect to the "air-quality" database and print the number of documents in the "nairobi" collection.

Retrieving Data¶

Now that we know how many documents the "lagos" collection contains, let's take a closer look at what's there. The first thing you'll notice is that the output starts out with a curly bracket ({), and ends with a curly bracket (}). That tells us that this information is a dictionary. To access documents in the collection, we'll use two methods: find and find_one. As you might expect, find will retrieve all the documents, and find_one will bring back only the first document. For now, let's stick to find_one; we'll come back to find later.

Just like everywhere else, we'll need to assign a variable name to whatever comes back, so let's call this one result.

Key-Value Pairs¶

There's a lot going on here! Let's work from the bottom up, starting with this:

{
    'temperature': 27.0,
    'timestamp': datetime.datetime(2017, 9, 6, 13, 18, 10, 120000)
}

The actual data is labeled temperature and timestamp, and if seeing it presented this way seems familiar, that's because what you're seeing at the bottom are two key-value pairs. In PyMongo, "_id" is always the primary key. Primary keys are the column(s) which contain values that uniquely identify each row in a table; we'll talk about that more in a minute.

Metadata¶

Next, we have this:

'metadata': { 'lat': 6.602,
              'lon': 3.351,
              'measurement': 'temperature',
              'sensor_id': 9,
              'sensor_type': 'DHT11',
              'site': 2}

This is the document's metadata. Metadata is data about the data. If you’re working with a database, its data is the information it contains, and its metadata describes what that information is. In MongoDB, each document often has metadata of its own. If we go back to the example of your email account, each message in your Sent folder includes both the message itself and information about when you sent it and who you sent it to; the message is data, and the other information is metadata.

The metadata we see in this block of code tells us what the key-value pairs from the last code block mean, and where the information stored there comes from. There's location data, a line telling us what about the format of the key-value pairs, some information about the equipment used to gather the data, and where the data came from.

Identifiers¶

Finally, at the top, we have this:

{ 
    '_id': ObjectId('6126f1780e45360640bf240a')
}

This is the document's unique identifier, which is similar to the index label for each row in a pandas DataFrame.

Practice

Try it yourself! Retrieve a single document from the "nairobi" collection, and print the result.

Analyzing Data¶

Now that we've seen what a document looks like in this collection, let's start working with what we've got. Since our metadata includes information about each sensor's "site", we might be curious to know how many sites are in the "lagos" collection. To do that, we'll use the distinct method, like this:

Notice that in order to grab the "site" number, we needed to include the "metadata" tag.

This tells us that there are 2 sensor sites in Lagos: one labeled 3 and the other labeled 4.

Let's go further. We know that there are two sensor sites in Lagos, but we don't know how many documents are associated with each site. To find that out, we'll use the count_documents method for each site.

Practice

Try it yourself! Find out how many sensor sites are in Nairobi, what their labels are, and how many documents are associated with each one.

Now that we know how many documents are associated with each site, let's keep drilling down and find the number of readings for each site. We'll do this with the aggregate method.

Before we run it, let's take a look at some of what's happening in the code here. First, you'll notice that there are several dollar signs ($) in the list. This is telling the collection that we want to create something new. Here, we're saying that we want there to be a new group, and that the new group needs to be updated with data from metadata.site, and then updated again with data from count.

There's also a new field: "_id". In PyMongo, "_id" is always the primary key. Primary keys are the fields which contain values that uniquely identify each row in a table.

Let's run the code and see what happens:

With that information in mind, we might want to know what those readings actually are. Since we're really interested in measures of air quality, let's take a look at the P2 values in the "lagos" collection. P2 measures the amount of particulate matter in the air, which in this case is something called PM 2.5. If we wanted to get all the documents in a collection, we could, but that would result in an unmanageably large number of records clogging up the memory on our machines. Instead, let's use the find method and use limit to make sure we only get back the first 3.

Practice

Try it yourself! Find out how many sensor sites are in Nairobi, what their labels are, how many documents are associated with each one, and the number of observations from each site. Then, return the first three documents with the value P2.

So far, we've been dealing with relatively small subsets of the data in our collections, but what if we need to work with something bigger? Let's start by using distinct to remind ourselves of the kinds of data we have at our disposal.

There are also comparison query operators that can be helpful for filtering the data. In total, we have

• $gt: greater than (>)
• $lt: less than (<)
• $gte: greater than equal to (>=)
• $lte: less than equal to (<= )

Let's use the timestamp to see how we can use these operators to select different documents:

Practice

Try it yourself! Find three documents with timestamp greater than or equal to and less than or equal the date December 12, 2018 — datetime.datetime(2018, 12, 1, 0, 0, 6, 767000).

Updating Documents¶

We can also update documents by passing some filter and new values using update_one to update one record or update_many to update many records. Let's look at an example. Before updating, we have this record showing like this:

Now we are updating the sensor type from "SDS011" to "SDS", we first select all records with sensor type equal to "SDS011", then set the new value to "SDS":

Now we can see all records have changed:

We can change it back:

Aggregation¶

Since we're looking for big numbers, we need to figure out which one of those dimensions has the largest number of measurements by aggregating the data in each document. Since we already know that site 3 has significantly more documents than site 2, let's start looking at site 3. We can use the $match syntax to only select site 3 data. The code to do that looks like this:

Practice

Try it yourself! Find the number of each measurement type at site 29 in Nairobi.

After aggregation, there is another useful operator called $project, which allows you to specify which fields to display by adding new fields or deleting fields. Using the Nairobi data from site 29, we can first count each sensor type:WQU WorldQuant University Applied Data Science Lab QQQQ

We can see there are two sensor types and the corresponding counts. If we only want to display what are the types but do not care about the counts, we can suppress the count filed by setting it at 0 in $project:

The $project syntax is also useful for deleting the intermediate fields that we used to generate our final fields but no longer need. In the following example, let's calculate the date difference for each sensor type. We'll first use the aggregation method to get the start date and last date.

Then we can calculate the date difference using $dateDiff, which gets the date difference through specifying the start date, end date and unit for timestamp data. We can see from the results above that the dates, are very close to each other. The only differences are in the minutes, so we can specify the unit as minute to show the difference. Since we don't need the start date and end dates, we can define a "dateDiff" field inside $project, so that it will be shown in the final display:

If we specify unit as day, it will show the difference between the dates:

Practice

Try it yourself find the date difference for each measurement type at site 29 in Nairobi.

We can do more with the date data using $dateTrunc, which truncates datetime data. We need to specify the datetime data, which can be a Date, a Timestamp, or an ObjectID. Then we need to specify the unit (year, month, day, hour, minute, second) and binSize (numerical variable defining the size of the truncation). Let's check the example below, where we group data by the month using $dateTrunc and then count how many observations there are for each month.

Practice

Try it yourself! Truncate date by week and count at site 29 in Nairobi.

Finishing Up¶

So far, we've connected to a server, accessed that server with a client, found the collection we were looking for within a database, and explored that collection in all sorts of different ways. Now it's time to get the data we'll actually need to build a model, and store that in a way we'll be able to use.

Let's use find to retrieve the PM 2.5 data from site 3. And, since we don't need any of the metadata to build our model, let's strip that out using the projection argument. In this case, we're telling the collection that we only want to see "timestamp" and "P2". Keep in mind that we limited the number of records we'll get back to 3 when we defined result above.

Finally, we'll use pandas to read the extracted data into a DataFrame, making sure to set timestamp as the index:

Practice

Try it yourself! Retrieve the PM 2.5 data from site 29 in Nairobi and strip out the metadata to create a DataFrame that shows only timestamp and P2. Print the result.

References & Further Reading¶

• Further reading about servers and clients
• Definitions from the MongoDB documentation
• Information on Iterators
• MongoDB documentation in Aggregation

Copyright 2022 WorldQuant University. This content is licensed solely for personal use. Redistribution or publication of this material is strictly prohibited.

3.1. Wrangling Data with MongoDB 🇰🇪

Task 3.1.1: Instantiate a PrettyPrinter, and assign it to the variable pp.

• Construct a PrettyPrinter instance in pprint.

Prepare Data¶

Connect¶

Task 3.1.2: Create a client that connects to the database running at localhost on port 27017.

• What's a database client?
• What's a database server?
• Create a client object for a MongoDB instance.

Explore¶

Task 3.1.3: Print a list of the databases available on client.

• What's an iterator?
• List the databases of a server using PyMongo.
• Print output using pprint.

Task 3.1.4: Assign the "air-quality" database to the variable db.

• What's a MongoDB database?
• Access a database using PyMongo.

Task 3.1.5: Use the list_collections method to print a list of the collections available in db.

• What's a MongoDB collection?
• List the collections in a database using PyMongo.

Task 3.1.6: Assign the "nairobi" collection in db to the variable name nairobi.

• Access a collection in a database using PyMongo.

Task 3.1.7: Use the count_documents method to see how many documents are in the nairobi collection.

• What's a MongoDB document?
• Count the documents in a collection using PyMongo.

Task 3.1.8: Use the find_one method to retrieve one document from the nairobi collection, and assign it to the variable name result.

• What's metadata?
• What's semi-structured data?
• Retrieve a document from a collection using PyMongo.

Task 3.1.9: Use the distinct method to determine how many sensor sites are included in the nairobi collection.

• Get a list of distinct values for a key among all documents using PyMongo.

Task 3.1.10: Use the count_documents method to determine how many readings there are for each site in the nairobi collection.

• Count the documents in a collection using PyMongo. WQU WorldQuant University Applied Data Science Lab QQQQ

Task 3.1.11: Use the aggregate method to determine how many readings there are for each site in the nairobi collection.

• Perform aggregation calculations on documents using PyMongo.

Task 3.1.12: Use the distinct method to determine how many types of measurements have been taken in the nairobi collection.

• Get a list of distinct values for a key among all documents using PyMongo.

Task 3.1.13: Use the find method to retrieve the PM 2.5 readings from all sites. Be sure to limit your results to 3 records only.

• Query a collection using PyMongo.

Task 3.1.14: Use the aggregate method to calculate how many readings there are for each type ("humidity", "temperature", "P2", and "P1") in site 6.

• Perform aggregation calculations on documents using PyMongo.

Task 3.1.15: Use the aggregate method to calculate how many readings there are for each type ("humidity", "temperature", "P2", and "P1") in site 29.

• Perform aggregation calculations on documents using PyMongo.

Import¶

Task 3.1.16: Use the find method to retrieve the PM 2.5 readings from site 29. Be sure to limit your results to 3 records only. Since we won't need the metadata for our model, use the projection argument to limit the results to the "P2" and "timestamp" keys only.

• Query a collection using PyMongo.

Task 3.1.17: Read records from your result into the DataFrame df. Be sure to set the index to "timestamp".

• Create a DataFrame from a dictionary using pandas.

Copyright 2022 WorldQuant University. This content is licensed solely for personal use. Redistribution or publication of this material is strictly prohibited.

Databases: SQL

Working with SQL Databases¶

A database is a collection of interrelated data. The primary goal of a database is to store and retrieve information in a convenient and efficient way. There are many types of databases. In this section, we will be dealing with a relational database. A relational database is a widely used database model that consists of a collection of uniquely named tables used to store information. The structure of a database model with its tables, constraints, and relationships is called a schema.

A Structured Query Language (SQL), is used to retrieve information from a relational database. SQL is one of the most commonly used database languages. It allows data stored in a relational database to be queried, modified, and manipulated easily with basic commands. SQL powers database engines like MySQL, SQL Server, SQLite, and PostgreSQL. The examples and projects in this course will use SQLite.

A table refers to a collection of rows and columns in a relational database. When reading data into a pandas DataFrame, an index can be defined, which acts as the label for every row in the DataFrame.

Connecting to a Database¶

ipython-sql¶

Magic Commands¶

Jupyter notebooks can run code that is not valid Python code but still affect the notebook . These special commands are called magic commands. Magic commands can have a range of properties. Some commonly used magic functions are below:

We will be leveraging magic commands to work with a SQLite database.

ipython-sql¶

ipython-sql allows you to write SQL code directly in a Jupyter Notebook. The %sql (or %%sql) magic command is added to the beginning of a code block and then SQL code can be written.

Connecting with ipython-sql¶

We can connect to a database using the %sql magic function:

sqlite3¶

We can also connect to the same database using the sqlite3 package:

Querying a Database¶

Building Blocks of the Basic Query¶

There are six common clauses used for querying data:

All clauses may be used together, but SELECT and FROM are the only required clauses. The format of clauses is in the example query below:

SELECT column1, column2
FROM table_name
WHERE "conditions"
GROUP BY "column-list"
HAVING "conditions"
ORDER BY "column-list"

SELECT and FROM¶

You can use SELECT * to select all columns in a table. FROM specifies the table in the database to query. LIMIT 5 will select only the first five rows.

Example

You can also use SELECT to select certain columns in a table

Practice

Try it yourself! Use SELECT to select the district_id column from the id_map table.

We can also assign an alias or temporary name to a column using the AS command. Aliases can also be used on a table. See the example below, which assigns the alias household_number to household_id

Practice

Try it yourself! Use SELECT, FROM, AS, and LIMIT to select the first 5 rows from the id_map table. Rename the district_id column to district_number.

Filtering and Sorting Data¶

SQL provides a variety of comparison operators that can be used with the WHERE clause to filter the data.

For example, to select the first 5 homes in Ramechhap (district 2):

Practice

Try it yourself! Use WHERE to select the row with household_id equal to 13735001

Aggregating Data¶

Aggregation functions take a collection of values as inputs and return one value as the output. The table below gives the frequently used built-in aggregation functions:

Use the COUNT function to find the number of observations in the id_map table that come from Ramechhap (district 2):

Aggregation functions are frequently used with a GROUP BY clause to perform the aggregation on groups of data. For example, the query below returns the count of observations in each District:

DISTINCT is a keyword to select unique records in a query result. For example, if we want to know the unique values in the district_id column:

Practice

Try it yourself! Use DISTINCT to count the number of unique values in the vdcmun_id column.

DISTINCT and COUNT can be used in combination to count the number of distinct records. For example, if we want to know the number of unique values in the district_id column:

Practice

Try it yourself! Use DISTINCT and COUNT to count the number of unique values in the vdcmun_id column.

Joining Tables¶

Joins link data from two or more tables together by using a column that is common between the two tables. The basic syntax for a join is below, where table1 and table2 refer to the two tables being joined, column1 and column2 refer to columns to be returned from both tables, and ID refers to the common column in the two tables.

SELECT table1.column1,
       table2.column2
FROM table_1
JOIN table2 ON table1.id = table1.id

We'll explore the concept of joins by first identifying a single household that we'd like to pull in building information for. For example, let's say we want to see the corresponding foundation_type for the first home in Ramechhap (District 1). We'll start by looking at this single record in the id_map table.